#---
#do this on darjeeling server
#---
#connect to census database
library(RPostgreSQL)
con <- dbConnect(PostgreSQL(), user= "stuff", password= "stuff", dbname= "census")

#Ontario population for 1981
#ontPop <- NULL
#ontRes <- dbSendQuery(con, 
#	"select * from y1981.ca_pr_pop where prname like 'ONTARIO%'")
#ontPoptemp <- fetch(ontRes, -1)
#ontPop <- c(ontPop, list(data.frame(year = 1981, ontPoptemp)))
#dbClearResult(ontRes)

#division populations for Essex County (3537), Lambton County (3538) and Middlesex County (3539) for 1981
#cdPop <- NULL
#cdRes <- dbSendQuery(con, 
#	"select * from y1981.ca_cd_pop where cdname like 'ESSEX%' or cdname like 'LAMBTON%' or cdname like 'MIDDLESEX%'")
#cdPoptemp <- fetch(cdRes, -1)
#cdPop <- c(cdPop, list(data.frame(year = rep(1981,3), cduid = 3537:3539, cdPoptemp)))
#dbClearResult(ontRes)

#Ontario and division populations for Essex County (3537), Lambton County (3538) and Middlesex County (3539) between 1986 and 2006
ontPop <- NULL
cdPop <- NULL
year <- seq(1986, 2006, by = 5) #census years
for(census in year)
{
	ontRes <- dbSendQuery(con, 
		paste("select * from y", census, ".ca_pr_pop where prname like 'Ontario%' or prname like 'ONTARIO%'", sep = ""))
	ontPoptemp <- fetch(ontRes, -1)
	ontPop <- c(ontPop, list(data.frame(year = census, ontPoptemp)))
	dbClearResult(ontRes)

	cdRes <- dbSendQuery(con, 
		paste("select * from y", census, ".ca_cd_pop where cduid like '3537' or cduid like '3538' or cduid like '3539'", sep = ""))
	cdPoptemp <- fetch(cdRes, -1)
	cdPop <- c(cdPop, list(data.frame(year = census, cdPoptemp)))
	dbClearResult(cdRes)
}
cdPop[[2]]$cdname <- cdPop[[3]]$cdname

rm(con, ontRes, cdRes, ontPoptemp, cdPoptemp, year)
save.image("census_data.RData")
